﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Data.SqlClient;
using Candy.Web.Data;
using Candy.Web;

namespace Candy.Web.Model
{
    public static class DatabaseManager
    {
        internal const string SqlRestoreTemplate = @"RESTORE DATABASE [CandyDB-$PROJECT$] FROM  DISK = N'E:\MSSQL\MSSQL10.MSSQLSERVER\MSSQL\Backup\Candy-template.bak' WITH  FILE = 1,  MOVE N'CandyDB_Data' TO N'E:\MSSQL\MSSQL10.MSSQLSERVER\MSSQL\DATA\CandyDB-$PROJECT$.mdf',  MOVE N'CandyDB_Log' TO N'E:\MSSQL\MSSQL10.MSSQLSERVER\MSSQL\DATA\CandyDB-$PROJECT$_1.ldf',  NOUNLOAD,  STATS = 10";
        internal const string ImportDatabasesTemplate = @"INSERT INTO [CandyDB-$PROJECT$].[dbo].[Database] ([MasterUId] ,[Name],[Version],[DisplayName],[IsSecondaryDB],[IsFishingDB], [FishingEnabled] ,[LocalDBName],[Collection]) SELECT [UId],[Name],[Version],[DisplayName],[IsSecondaryDB],[IsFishingDB],[IsFishingDB],[LocalDBName],[Collection]  FROM [CandyDB-Master].[dbo].[Database]";
        internal const string ImportGenotypeTemplate = @"INSERT INTO [CandyDB-$PROJECT$].[dbo].[Genotype]([MasterUId],[Genus],[Species],[Variety],[Name]) SELECT [UId], [Genus],[Species],[Variety], [Name] FROM [CandyDB-Master].[dbo].[Genotype]";
        internal const string InsertOwnerTemplate = @"INSERT INTO [CandyDB-{0}].[dbo].[User]([UId],[UserName]) values ('{1}', '{2}')";

        internal const string ConnStrTemplate = @"Data Source=galiano.biol.uvic.ca;Initial Catalog=CandyDB-$PROJECT$;User ID=candyadmin;Password=Poplar47";

        public static int CreateProjectDB(ProjectDto project, Version version, Guid ownerUid, string ownerName)
        {
            // SqlConnection sqlConnection = new SqlConnection(Properties.Settings.Default.Candy_templateConnectionString);
            SqlCommand myCommand = new SqlCommand();
            myCommand.Connection = new SqlConnection(global::System.Configuration.ConfigurationManager.ConnectionStrings["CandyMasterAsSA"].ConnectionString);
            myCommand.CommandText = SqlRestoreTemplate.Replace("$PROJECT$", project.Name.Trim());
            myCommand.Connection.Open();
            int result = myCommand.ExecuteNonQuery();
            myCommand.Connection.Close();

            myCommand = new SqlCommand();
            myCommand.Connection = new SqlConnection(global::System.Configuration.ConfigurationManager.ConnectionStrings["CandyMaster"].ConnectionString);
            myCommand.CommandText = ImportDatabasesTemplate.Replace("$PROJECT$", project.Name.Trim());
            myCommand.Connection.Open();
            int result2 = myCommand.ExecuteNonQuery();
            myCommand.Connection.Close();

            myCommand = new SqlCommand();
            myCommand.Connection = new SqlConnection(global::System.Configuration.ConfigurationManager.ConnectionStrings["CandyMaster"].ConnectionString);
            myCommand.CommandText = ImportGenotypeTemplate.Replace("$PROJECT$", project.Name.Trim());
            myCommand.Connection.Open();
            int result3 = myCommand.ExecuteNonQuery();
            myCommand.Connection.Close();

            myCommand = new SqlCommand();
            myCommand.Connection = new SqlConnection(global::System.Configuration.ConfigurationManager.ConnectionStrings["CandyMaster"].ConnectionString);
            myCommand.CommandText = string.Format(InsertOwnerTemplate, project.Name.Trim(), ownerUid, ownerName);
            myCommand.Connection.Open();
            int result4 = myCommand.ExecuteNonQuery();
            myCommand.Connection.Close();

            using (AuthDatabaseDataContext context = new AuthDatabaseDataContext())
            {
                Project pr = new Project()
                {
                    Name = project.Name.Trim(),
                    Description = project.Description,
                    ConnectionString = ConnStrTemplate.Replace("$PROJECT$", project.Name.Trim()),
                    CandyVersion = version.ToString()

                };
                context.Projects.InsertOnSubmit(pr);
                context.SubmitChanges();
                return pr.Id;
            }
        }
    }
}